from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func, and_, desc, update, case, join, outerjoin
from typing import List, Optional
from module_user.entity.do.bg_user_affiliate_do import BgUserAffiliate
from module_user.entity.do.bg_affiliate_relation_do import BgAffiliateRelation
from module_user.entity.do.bg_affiliate_commission_do import BgAffiliateCommission
from module_admin.entity.do.user_do import SysUser
from module_product.entity.do.bg_order_do import BgOrder
from decimal import Decimal


class BgUserAffiliateDao:
    """用户推广信息DAO"""

    async def get_by_user_id(self, db: AsyncSession, user_id: int) -> Optional[BgUserAffiliate]:
        """根据用户ID获取推广信息"""
        result = await db.execute(
            select(BgUserAffiliate).filter(
                BgUserAffiliate.user_id == user_id,
                BgUserAffiliate.del_flag == '0'
            )
        )
        return result.scalar_one_or_none()

    async def get_by_affiliate_code(self, db: AsyncSession, affiliate_code: str) -> Optional[BgUserAffiliate]:
        """根据推广码获取推广信息"""
        result = await db.execute(
            select(BgUserAffiliate).filter(
                BgUserAffiliate.affiliate_code == affiliate_code,
                BgUserAffiliate.del_flag == '0',
                BgUserAffiliate.status == 1
            )
        )
        return result.scalar_one_or_none()

    async def create_affiliate_info(self, db: AsyncSession, user_id: int, affiliate_code: str, commission_rate: Decimal = Decimal('5.00')) -> BgUserAffiliate:
        """创建推广信息"""
        from datetime import datetime
        affiliate_info = BgUserAffiliate(
            user_id=user_id,
            affiliate_code=affiliate_code,
            commission_rate=commission_rate,
            total_commission=Decimal('0.00'),
            status=1,
            create_time=datetime.now(),
            update_time=datetime.now(),
            del_flag='0',
            create_by=user_id,
            dept_id=204
        )
        db.add(affiliate_info)
        await db.commit()
        await db.refresh(affiliate_info)
        return affiliate_info

    async def update_total_commission(self, db: AsyncSession, user_id: int, commission_amount: Decimal, auto_commit=True):
        """更新累计返利金额"""
        from sqlalchemy import update
        await db.execute(
            update(BgUserAffiliate)
            .filter(
                BgUserAffiliate.user_id == user_id,
                BgUserAffiliate.del_flag == '0'
            )
            .values(total_commission=BgUserAffiliate.total_commission + commission_amount)
        )
        await db.flush()
        if auto_commit:
            await db.commit()

    async def get_affiliate_stats(self, db: AsyncSession, user_id: int) -> dict:
        """获取推广统计信息"""
        # 获取邀请人数
        invitee_result = await db.execute(
            select(func.count(BgAffiliateRelation.invitee_id)).filter(
                BgAffiliateRelation.inviter_id == user_id,
                BgAffiliateRelation.status == 1
            )
        )
        invitee_count = invitee_result.scalar() or 0

        # 获取总订单数和金额
        order_result = await db.execute(
            select(
                func.count(BgAffiliateCommission.id).label('total_orders'),
                func.coalesce(func.sum(BgAffiliateCommission.order_amount), 0).label('total_order_amount'),
                func.coalesce(func.sum(BgAffiliateCommission.commission_amount), 0).label('total_commission')
            ).filter(
                BgAffiliateCommission.inviter_id == user_id,
                BgAffiliateCommission.del_flag == '0'
            )
        )
        order_stats = order_result.first()

        # 获取待结算和已结算返利
        from sqlalchemy import case
        commission_result = await db.execute(
            select(
                func.coalesce(func.sum(
                    case(
                        (BgAffiliateCommission.status == 0, BgAffiliateCommission.commission_amount),
                        else_=0
                    )
                ), 0).label('pending_commission'),
                func.coalesce(func.sum(
                    case(
                        (BgAffiliateCommission.status == 1, BgAffiliateCommission.commission_amount),
                        else_=0
                    )
                ), 0).label('settled_commission')
            ).filter(
                BgAffiliateCommission.inviter_id == user_id,
                BgAffiliateCommission.del_flag == '0'
            )
        )
        commission_stats = commission_result.first()

        return {
            'total_invitees': invitee_count,
            'total_orders': order_stats.total_orders or 0,
            'total_order_amount': order_stats.total_order_amount or Decimal('0.00'),
            'total_commission': order_stats.total_commission or Decimal('0.00'),
            'pending_commission': commission_stats.pending_commission or Decimal('0.00'),
            'settled_commission': commission_stats.settled_commission or Decimal('0.00')
        }

    async def get_invitee_list(self, db: AsyncSession, user_id: int, page: int = 1, size: int = 20) -> List[dict]:
        """获取被邀请人列表"""
        # 查询被邀请人信息
        query = select(
            BgAffiliateRelation.invitee_id,
            SysUser.email,
            BgAffiliateRelation.invite_time.label('register_time'),
            func.count(BgAffiliateCommission.id).label('total_orders'),
            func.coalesce(func.sum(BgAffiliateCommission.order_amount), 0).label('total_order_amount'),
            func.coalesce(func.sum(BgAffiliateCommission.commission_amount), 0).label('total_commission'),
            func.max(BgAffiliateCommission.create_time).label('last_order_time')
        ).select_from(
            BgAffiliateRelation
        ).join(
            SysUser, BgAffiliateRelation.invitee_id == SysUser.user_id
        ).outerjoin(
            BgAffiliateCommission, and_(
                BgAffiliateCommission.inviter_id == user_id,
                BgAffiliateCommission.invitee_id == BgAffiliateRelation.invitee_id,
                BgAffiliateCommission.del_flag == '0'
            )
        ).filter(
            BgAffiliateRelation.inviter_id == user_id,
            BgAffiliateRelation.status == 1
        ).group_by(
            BgAffiliateRelation.invitee_id,
            SysUser.email,
            BgAffiliateRelation.invite_time
        ).order_by(desc(BgAffiliateRelation.invite_time))

        # 分页
        offset = (page - 1) * size
        query = query.offset(offset).limit(size)
        
        result = await db.execute(query)
        results = result.all()

        return [
            {
                'user_id': row.invitee_id,
                'email': row.email,
                'register_time': row.register_time,
                'total_orders': row.total_orders or 0,
                'total_order_amount': row.total_order_amount or Decimal('0.00'),
                'total_commission': row.total_commission or Decimal('0.00'),
                'last_order_time': row.last_order_time
            }
            for row in results
        ]

    async def get_commission_records(self, db: AsyncSession, user_id: int, page: int = 1, size: int = 20) -> List[dict]:
        """获取返利记录列表"""
        query = select(
            BgAffiliateCommission,
            SysUser.email.label('invitee_email'),
            BgOrder.order_no
        ).select_from(
            BgAffiliateCommission
        ).join(
            SysUser, BgAffiliateCommission.invitee_id == SysUser.user_id
        ).join(
            BgOrder, BgAffiliateCommission.order_id == BgOrder.id
        ).filter(
            BgAffiliateCommission.inviter_id == user_id,
            BgAffiliateCommission.del_flag == '0'
        ).order_by(desc(BgAffiliateCommission.create_time))

        # 分页
        offset = (page - 1) * size
        query = query.offset(offset).limit(size)
        
        result = await db.execute(query)
        results = result.all()

        return [
            {
                'id': row.BgAffiliateCommission.id,
                'invitee_email': row.invitee_email,
                'order_no': row.order_no,
                'order_amount': row.BgAffiliateCommission.order_amount,
                'commission_rate': row.BgAffiliateCommission.commission_rate,
                'commission_amount': row.BgAffiliateCommission.commission_amount,
                'status': row.BgAffiliateCommission.status,
                'settle_time': row.BgAffiliateCommission.settle_time,
                'create_time': row.BgAffiliateCommission.create_time
            }
            for row in results
        ]

    # 管理端方法
    async def get_admin_affiliate_list(self, db: AsyncSession, page: int, size: int, 
                                     email: str = None, affiliate_code: str = None, 
                                     status: int = None) -> dict:
        """获取推广返利列表（管理端）"""
        # 构建查询条件
        conditions = [BgUserAffiliate.del_flag == '0']
        
        if email:
            conditions.append(SysUser.email.like(f'%{email}%'))
        if affiliate_code:
            conditions.append(BgUserAffiliate.affiliate_code.like(f'%{affiliate_code}%'))
        if status is not None:
            conditions.append(BgUserAffiliate.status == status)

        # 查询总数
        count_query = select(func.count(BgUserAffiliate.id)).select_from(
            join(BgUserAffiliate, SysUser, BgUserAffiliate.user_id == SysUser.user_id)
        ).filter(*conditions)
        
        count_result = await db.execute(count_query)
        total = count_result.scalar() or 0

        # 查询列表数据
        query = select(
            BgUserAffiliate.user_id,
            SysUser.email,
            BgUserAffiliate.affiliate_code,
            BgUserAffiliate.commission_rate,
            BgUserAffiliate.total_commission,
            BgUserAffiliate.status,
            BgUserAffiliate.ali_pay_account,
            BgUserAffiliate.ali_pay_name,
            BgUserAffiliate.create_time,
            # 统计信息
            func.count(BgAffiliateRelation.invitee_id).label('total_invitees'),
            func.count(BgAffiliateCommission.id).label('total_orders'),
            # 待结算和已结算返利统计
            func.coalesce(func.sum(
                case(
                    (BgAffiliateCommission.status == 0, BgAffiliateCommission.commission_amount),
                    else_=0
                )
            ), 0).label('pending_commission'),
            func.coalesce(func.sum(
                case(
                    (BgAffiliateCommission.status == 1, BgAffiliateCommission.commission_amount),
                    else_=0
                )
            ), 0).label('settled_commission')
        ).select_from(
            join(BgUserAffiliate, SysUser, BgUserAffiliate.user_id == SysUser.user_id)
            .outerjoin(BgAffiliateRelation, BgAffiliateRelation.inviter_id == BgUserAffiliate.user_id)
            .outerjoin(BgAffiliateCommission, BgAffiliateCommission.inviter_id == BgUserAffiliate.user_id)
        ).filter(*conditions).group_by(
            BgUserAffiliate.user_id,
            SysUser.email,
            BgUserAffiliate.affiliate_code,
            BgUserAffiliate.commission_rate,
            BgUserAffiliate.total_commission,
            BgUserAffiliate.status,
            BgUserAffiliate.ali_pay_account,
            BgUserAffiliate.ali_pay_name,
            BgUserAffiliate.create_time
        ).order_by(desc(BgUserAffiliate.create_time))

        # 分页
        offset = (page - 1) * size
        query = query.offset(offset).limit(size)
        
        result = await db.execute(query)
        results = result.all()

        records = [
            {
                'user_id': row.user_id,
                'email': row.email,
                'affiliate_code': row.affiliate_code,
                'commission_rate': row.commission_rate,
                'total_commission': row.total_commission,
                'status': row.status,
                'ali_pay_account': row.ali_pay_account,
                'ali_pay_name': row.ali_pay_name,
                'create_time': row.create_time,
                'total_invitees': row.total_invitees,
                'total_orders': row.total_orders,
                'pending_commission': row.pending_commission,
                'settled_commission': row.settled_commission
            }
            for row in results
        ]

        return {
            "records": records,
            "total": total,
            "current": page,
            "size": size
        }

    async def update_affiliate_status(self, db: AsyncSession, user_id: int, status: int):
        """更新推广状态"""
        await db.execute(
            update(BgUserAffiliate)
            .filter(
                BgUserAffiliate.user_id == user_id,
                BgUserAffiliate.del_flag == '0'
            )
            .values(status=status)
        )
        await db.commit()
        return {"success": True}

    async def get_pending_commissions_by_user(self, db: AsyncSession, user_id: int) -> List[int]:
        """获取用户的所有待结算返利记录ID"""
        result = await db.execute(
            select(BgAffiliateCommission.id).filter(
                BgAffiliateCommission.inviter_id == user_id,
                BgAffiliateCommission.status == 0,  # 待结算
                BgAffiliateCommission.del_flag == '0'
            )
        )
        return [row[0] for row in result.all()]

    async def update_ali_pay_info(self, db: AsyncSession, user_id: int, ali_pay_account: str = None, ali_pay_name: str = None):
        """更新支付宝信息"""
        update_data = {}
        if ali_pay_account is not None:
            update_data['ali_pay_account'] = ali_pay_account
        if ali_pay_name is not None:
            update_data['ali_pay_name'] = ali_pay_name
        
        if update_data:
            await db.execute(
                update(BgUserAffiliate)
                .filter(
                    BgUserAffiliate.user_id == user_id,
                    BgUserAffiliate.del_flag == '0'
                )
                .values(**update_data)
            )
            await db.commit()
        return {"success": True}
